const mysql = require("mysql2");

class MySQLDatabase {
  constructor(config) {
    this.pool = mysql.createPool(config);
  }

  /**
   * 执行 SQL 查询
   * @param {string} sql SQL 语句
   * @param {Array} params 参数数组
   * @returns {Promise<any>} 查询结果
   */
  query(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.pool.getConnection((err, connection) => {
        if (err) {
          reject(err);
          connection.release();
          return;
        }

        connection.query(sql, params, (err, results) => {
          if (err) {
            reject(err);
            connection.release();
            return;
          }

          resolve(results);
          connection.release();
        });
      });
    });
  }

  /**
   * 插入数据
   * @param {string} table 表名
   * @param {Object} data 要插入的数据对象 { field1: value1, field2: value2 }
   * @returns {Promise<any>} 插入结果
   */
  async insert(table, data) {
    const keys = Object.keys(data);
    const values = Object.values(data);
    const placeholders = keys.map(() => "?").join(", ");

    const sql = `INSERT INTO ?? (${keys.join(", ")}) VALUES (${placeholders})`;

    return this.query(sql, [table, ...values]);
  }

  /**
   * 更新数据
   * @param {string} table 表名
   * @param {Object} data 要更新的数据 { field1: value1, field2: value2 }
   * @param {string} where WHERE 条件（如 "id = ?"）
   * @param {Array} whereParams WHERE 条件参数
   * @returns {Promise<any>} 更新结果
   */
  update(table, data, where, whereParams) {
    const fields = Object.keys(data);
    const values = Object.values(data);
    const setClause = fields.map(field => `${field} = ?`).join(", ");

    const sql = `UPDATE ?? SET ${setClause} WHERE ${where}`;

    return this.query(sql, [table, ...values, ...whereParams]);
  }

  /**
   * 删除数据
   * @param {string} table 表名
   * @param {string} where WHERE 条件（如 "id = ?"）
   * @param {Array} whereParams WHERE 条件参数
   * @returns {Promise<any>} 删除结果
   */
  delete(table, where, whereParams) {
    const sql = `DELETE FROM ?? WHERE ${where}`;
    return this.query(sql, [table, ...whereParams]);
  }

  /**
   * 查询数据
   * @param {string} table 表名
   * @param {string} where WHERE 条件（如 "id = ?"）
   * @param {Array} whereParams WHERE 条件参数
   * @returns {Promise<any>} 查询结果
   */
  select(table, where = "1=1", whereParams = []) {
    const sql = `SELECT * FROM ?? WHERE ${where}`;
    return this.query(sql, [table, ...whereParams]);
  }

  /**
   * 关闭连接池
   */
  end() {
    this.pool.end();
  }
}

module.exports = MySQLDatabase;